/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package ciif;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.prefs.*;
import java.io.File;
import javax.swing.*;
import java.awt.*;


/**
 *
 * @author drano
 */
public class CIIFDatabase {
    private CIIFView main;
    private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
    private Preferences prefs;
    private Boolean hasLoaded = false;
    private Connection conn;
    private JFrame loadingFrame;
    private JProgressBar loadingProgressBar;
    private JButton finishButton;
    private CIIFConfigure finishConfigureFrame = null;
    private Boolean disableButton = true;
    private Boolean doFrame = false;
    private Boolean doButton = false;
    private PreparedStatement insertRecord;
    private PreparedStatement deleteRecord;
    private PreparedStatement updateRecord;
    private PreparedStatement recordExists;
    private PreparedStatement loadRecord;
    private PreparedStatement getRIL;

    public CIIFDatabase(CIIFView w) {
        main = w;
        prefs = Preferences.userNodeForPackage(this.getClass());
    }
    
    public Boolean checkDB() {
        return hasLoaded ? true : false;
    }

    public Connection getConn() {
        return conn;
    }
    
    public void setConn(Connection con) {
        conn = con;
    }

    public void doInsert(String imageid, String imagepath, String imagethumb, int imageviews, String imagegroup) {
        System.out.println("1");
        try {
            insertRecord.setString(1, imageid);
            insertRecord.setString(2, imagepath);
            insertRecord.setString(3, imagethumb);
            insertRecord.setInt(4, imageviews);
            insertRecord.setString(5, imagegroup);
            insertRecord.execute();
            conn.commit();
            System.out.println("Inserted imageid");
        } catch (SQLException sqle) {
            printSQLException(sqle);
        }
        System.out.println("2");
    }
    
    public ResultSet loadRecord(String imageid) {
        try {
            loadRecord.setMaxRows(1);
            loadRecord.setString(1, imageid);
            return loadRecord.executeQuery();
        } catch (SQLException sqle) {
            printSQLException(sqle);
            return null;
        }    
    }

    public ResultSet getRandomImageList(int count) {
        try {
            getRIL.setMaxRows(count);
            return getRIL.executeQuery();
        } catch (SQLException sqle) {
            printSQLException(sqle);
            return null;
        }
    }

    public Boolean recordExists(String imageid) {
        System.out.println(0);
        try {
            Statement s = conn.createStatement();
            s.setMaxRows(1);
            ResultSet srs = s.executeQuery("select * from CIIFImages order by random()");
            if (srs.next()) {
                System.out.println("Random image from db: ");
                System.out.println(srs.getString(1));
                System.out.println(srs.getString(2));
                System.out.println(srs.getString(3));
                System.out.println(srs.getInt(4));
                System.out.println(srs.getString(5));
            }
            recordExists.setString(1, imageid);
            ResultSet rs = recordExists.executeQuery();
            if (rs.next()) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException sqle) {
            printSQLException(sqle);
            return false;
        }
    }
    
    public void finishLoad() {
        hasLoaded = true;
        loadingFrame.dispose();
        if (doButton) {
            if (disableButton) {
                finishButton.setEnabled(true);
                finishButton.doClick();
                finishButton.setEnabled(false);
            } else {
                main.enableAll();
                finishButton.doClick();
            }
            finishButton = null;
            //main.enableAll();
        } else if (doFrame) {
            finishConfigureFrame.enableAll();
            finishConfigureFrame = null;
            main.enableAll();
        } else {
            main.enableAll();
        }
    }

    public void setSQL(PreparedStatement i, PreparedStatement u, PreparedStatement d, PreparedStatement re, PreparedStatement lr, PreparedStatement gril) {
        insertRecord = i;
        updateRecord = u;
        deleteRecord = d;
        recordExists = re;
        loadRecord = lr;
        getRIL = gril;
    }

    public Boolean loadDB(CIIFConfigure frame) {
        doFrame = true;
        finishConfigureFrame = frame;
        frame.disableAll();
        return loadDB();
    }

    public Boolean loadDB(javax.swing.JButton button, Boolean disable) {
        doButton = true;
        disableButton = disable;
        finishButton = button;
        return loadDB();
    }

    public Boolean loadDB() {
        File dbDir = new File(prefs.get("DBDIR", ""));
        if (! dbDir.isDirectory() || ! dbDir.canWrite()) {
            return false;
        }
        main.disableAll();
        loadingFrame = new JFrame("Loading Database...");
        loadingFrame.setSize(new Dimension(500, 120));
        loadingFrame.setLayout(new BorderLayout());
        loadingFrame.setDefaultCloseOperation(JFrame.DO_NOTHING_ON_CLOSE);
        loadingFrame.setLocationRelativeTo(CIIFApp.getApplication().getMainFrame());
        JLabel loadingLabel = new JLabel("<html><p>Please wait while the database is loaded.  If this is the first time running (or you've changed the database location) this operation may take up to serveral minutes...</p></html>");
        loadingLabel.setPreferredSize(new Dimension(400, 100));
        loadingFrame.getContentPane().add(loadingLabel, BorderLayout.CENTER);
        loadingProgressBar = new JProgressBar();
        loadingProgressBar.setIndeterminate(true);
        loadingProgressBar.setStringPainted(true);
        loadingProgressBar.setString("Loading Database...");
        loadingFrame.getContentPane().add(loadingProgressBar, BorderLayout.SOUTH);
        loadingFrame.pack();
        loadingFrame.setVisible(true);
        loadingFrame.toFront();
        System.setProperty("derby.system.home", prefs.get("DBDIR", ""));

        try {
            dbLoadTask task = new dbLoadTask(this);
            task.execute();
            try {
                wait();
            } catch(InterruptedException e) {
                System.out.println("InterruptedException caught");
            }
        } catch (Exception e) {
            System.out.println(e.toString());
        }

        Runtime.getRuntime().addShutdownHook(new Thread() {
            public void run() {
                endApp();
            }
        });;

        return true;
    }

    public void endApp() {
        try {
                // the shutdown=true attribute shuts down Derby
                DriverManager.getConnection("jdbc:derby:;shutdown=true");

                // To shut down a specific database only, but keeep the
                // engine running (for example for connecting to other
                // databases), specify a database in the connection URL:
                //DriverManager.getConnection("jdbc:derby:" + dbName + ";shutdown=true");
            }
            catch (SQLException se)
            {
                if (( (se.getErrorCode() == 50000)
                        && ("XJ015".equals(se.getSQLState()) ))) {
                    // we got the expected exception
                    System.out.println("Derby shut down normally");
                    // Note that for single database shutdown, the expected
                    // SQL state is "08006", and the error code is 45000.
                } else {
                    // if the error code or SQLState is different, we have
                    // an unexpected exception (shutdown failed)
                    System.err.println("Derby did not shut down normally");
                    printSQLException(se);
                }
            }
        }

    public static void printSQLException(SQLException e)    {
        // Unwraps the entire exception chain to unveil the real cause of the
        // Exception.
        while (e != null)
        {
            System.err.println("\n----- SQLException -----");
            System.err.println("  SQL State:  " + e.getSQLState());
            System.err.println("  Error Code: " + e.getErrorCode());
            System.err.println("  Message:    " + e.getMessage());
            // for stack traces, refer to derby.log or uncomment this:
            //e.printStackTrace(System.err);
            e = e.getNextException();
        }
    }

}

class dbLoadTask extends SwingWorker<Void, Void> {
    private CIIFDatabase db;
    private String driver = "org.apache.derby.jdbc.EmbeddedDriver";

    public dbLoadTask(CIIFDatabase d) {
        db = d;
    }

    @Override
    public Void doInBackground() {
        loadDriver();

        Connection conn = db.getConn();
  
        Statement s = null;
        try {
            String dbName = "CIIFDB";
            conn = DriverManager.getConnection("jdbc:derby:" + dbName + ";create=true");
            db.setConn(conn);
            System.out.println("Connected to database " + dbName);
            conn.setAutoCommit(false);
            s = conn.createStatement();
            try {
                s.executeQuery("select count(*) from CIIFImages");
            } catch (Exception e) {
                s.execute("CREATE TABLE APP.CIIFIMages" +
                          "(IMAGEID CHAR(254) NOT NULL," +
                           "IMAGEPATH VARCHAR(1024)," +
                            "IMAGETHUMB VARCHAR(1024)," +
                            "IMAGEVIEWS INTEGER," +
                            "IMAGEGROUP CHAR(254)," +
                            "PRIMARY KEY (IMAGEID))");
                conn.commit();
                System.out.println("Created empty table.");
                System.out.println(e.toString());
            }
           try {
                PreparedStatement recordExists = conn.prepareStatement("select imageid from CIIFImages where imageid=?");
                PreparedStatement loadRecord = conn.prepareStatement("select * from CIIFImages where imageID=?");
                PreparedStatement insertRecord = conn.prepareStatement("insert into CIIFImages (imageid, imagepath, imagethumb, imageviews, imagegroup) values (?, ?, ?, ?, ?)");
                PreparedStatement updateRecord = conn.prepareStatement("update CIIFImages set imagepath=?, imagethumb=?, imageviews=?, imagegroup=? WHERE imageid=?");
                PreparedStatement deleteRecord = conn.prepareStatement("delete from CIIFImages where imageid=?");
                PreparedStatement getRIL = conn.prepareStatement("select imageid from CIIFImages order by random()");
                System.out.println("Here2");
                db.setSQL(insertRecord, updateRecord, deleteRecord, recordExists, loadRecord, getRIL);
            } catch (SQLException sqle) {
                db.printSQLException(sqle);
            }

            conn.commit();
        } catch (SQLException sqle) {
            db.printSQLException(sqle);
            return null;

        }
        db.finishLoad();
        return null;

    }

    public void loadDriver() {
        try {
            Class.forName(driver).newInstance();
            System.out.println("Loaded the appropriate driver");
        } catch (ClassNotFoundException cnfe) {
            System.err.println("\nUnable to load the JDBC driver " + driver);
            System.err.println("Please check your CLASSPATH.");
            cnfe.printStackTrace(System.err);
        } catch (InstantiationException ie) {
            System.err.println(
                        "\nUnable to instantiate the JDBC driver " + driver);
            ie.printStackTrace(System.err);
        } catch (IllegalAccessException iae) {
            System.err.println(
                        "\nNot allowed to access the JDBC driver " + driver);
            iae.printStackTrace(System.err);
        }
    }
}
